The 640 MEG Shareware Studio 2/The 640 Meg Shareware Studio CD-ROM Volume II (Data Express)(1993).ISO/dbase/a4invoic.zip/INV.TXT

Text File | 1992-05-11 | 13KB | 249 lines

               Invoicing using Alpha FOUR Sets         05/11/92
               -------------------------------

A basic guide to setting up an invoicing system using Sets in Alpha
FOUR.

Many users purchase Alpha FOUR to use as an invoicing system. A
database is a logical choice for this task. Some invoicing systems
are simple enough so that a single (flat file) database setup will
work just fine. A basic single database invoicing system would
include fields such as INVOICE_NO, COMPANY, ADDRESS, LINE_ITEM1,
DESC1, PRICE1, LINE_ITEM2, DESC2, PRICE2,etc... This simple example
would allow for easy data entry and will be fine for the simplest of
invoicing systems, however, as your needs grow you may want to take
advantage of Alpha FOUR's powerful relational capability and create
an invoicing system using a Set.

There are two basic ways to set up an invoicing system in Alpha FOUR
using Sets and what follows are examples of these two alternatives.
The first section under each option contains a short description with
the advantages and disadvantages. The second section goes into more
detail about how to create the basic setup for each option. This
write-up is only a start. You will no doubt want to customize your
invoicing system to meet your own needs. At the end of this write-up
are some suggestions for enhancing your invoicing system.

Option 1
--------
An invoicing system with multiple line item records for each invoice.

Option 1 involves linking a main INVOICE database (one-to-many or
"All") to a child LINE_ITEM database. This option will have a
separate LINE_ITEM record for each line item. The two databases are
linked with the INVOICE_NO field as the linking field.

Advantages:
Saves disk space; it takes up only as much disk space as you need for
each new line item and you are not storing empty LINE_ITEM fields.
Allows you to input an unlimited number of line items for each
invoice. This is because line items are represented by separate child
records and line items (records) are added to the LINE_ITEM database
only as they are needed.
When Searching, Option 1 also has the advantage because there's only
1 Item number field (1 PRICE field and 1 description field, etc) for
all line items.
This method makes it much easier to determine the number of each
product sold for all invoices. Since the LINE_ITEM database contains
only one item per record you can simply print a report from the
LINE_ITEM database indexed and subgrouped on DESCRIPTION or ITEM_NO.
The primary method for applying the line items against an inventory
database is to use the Post utility. This method makes this posting
operation much simpler because there is only one field that will
contain the line items.

Disadvantages:
You will not be able to view multiple line items from one invoice on
a Form at the same time (you can however print a Report that will
look like a traditional invoice form).
You will not be able to see the total of all line items on a Form
(although you will be able to total the line items for an invoice on
a Report).
More difficult to apply payments against multiple line item records
and store the resulting balance in the database, however you can show
the balance on a Report.


1. The first step is to create an INVOICE database with a common
field such as INVOICE_NO (this field will be used to link this
database to the LINE_ITEM database). You can also include other
fields like NAME, ADDRESS, BILL_INFO and SHIP_INFO.

2. Next create a separate database for line items (LINE_ITEM). The
only field that must be included in both this database and the parent
database is the common field (e.g. INVOICE_NO). This field will be
used to link the two databases into a Set. Other fields you will want
to include are ITEM_NO, DESCRIPTION, QUANTITY and PRICE. For this
application there's no need to have PART1, PART2, etc because each
record in this LINE_ITEM database will represent one line item.

3. The next step is to link these databases together in a Set. Create
an index in the child database (LINE_ITEM) on the common field
(INVOICE_NO). Then link the two databases together into a Set with
the INVOICE database as the parent and LINE_ITEM as the child. Use
the INVOICE_NO index you just created in the child database as the
Linking Index, and set "Link to ?" to ALL.

4. To make data entry easier, create a custom Form under layouts that
includes both INVOICE information and LINE_ITEM information. During
data entry you will enter the INVOICE information right on that Form
and you can "Zoom" to the child (LINE_ITEM) database to enter each
line item. Remember that each line item is entered in a separate
record in the LINE_ITEM database.

5. You may want to create a Report to print these Invoices. The
Report should have one level of subgrouping (see the Reference manual
for information on subgrouping reports). On the Report's grouping
Parameters screen, the group 1 break field would be the INVOICE_NO
field. Place the fields from the INVOICE database into the group 1
header. Place the line item fields from the LINE_ITEM database into
the detail section. The group 1 footer is for any totals that you
need for each invoice number. For example you may want to create a
calculated field using the expression PRICE*QUANTITY and place this
in the group 1 footer section as a total to get a subtotal for that
invoice.

Option 2
--------

An invoicing system with multiple line item fields for each invoice.
Option 2 involves linking the main INVOICE database (one-to-one or
"First") to the child LINE_ITEM database. The main difference is that
the LINE_ITEM database in this second option contains fields
representing multiple line items (rather than a separate record for
each line item). You will be only linking to one record in the
LINE_ITEM database and this one record will contain all the line
items for a given invoice.

Advantages:
This method allows you to view all line items for an invoice on a
Form at one time.
You can also display the line item total on a view Form.
You can apply payments against the invoice total on the view Form. To
do this, add multiple payment fields to the INVOICE database and a
calculated BALANCE field: (TOTAL-PMT1+PMT2+PMT3).

Disadvantages:
This method may be more cumbersome to work with when searching (i.e.
an item may appear in any one of multiple LINE_ITEM fields).
The number of possible line items attached to a given invoice are
limited by the number of fields you have pre-defined; if your line
items exceed the pre-set number of fields, you will need to place the
additional line items on another invoice.
This method makes it more difficult to determine the number of each
product sold for all invoices because the same product may not appear
in the same product field in each record.

This second type of invoice system using Sets in Alpha FOUR is the
most popular and involves creating a LINE_ITEM database with multiple
line items in one record (i.e. fields called ITEM1, ITEM2, ITEM3,
PRICE1, PRICE2, PRICE3). Link the two databases together using a
linking index on INVOICE_NO as described above. In this scenario,
however, there will be only one LINE_ITEM record for each invoice.
That one record will contain all line items for that invoice.

These are not the only ways to create Invoicing systems with Alpha
FOUR, they are only suggestions. Please feel free to modify these to
meet your specific needs.


Enhancement Suggestions

Both Set options require you to zoom to the child database to enter
the line items. Because this requires additional keystrokes, this can
be seen as a disadvantage over a single database invoicing system.
Using lookups, applications, scripts and the post utility can make
zooming more efficient as you can see in the following examples.

Lookups; copying INVOICE_NO from parent to child:
Data entry can be made easier by creating a lookup in the child
database that pulls the invoice number from the last entered record
in the parent database. First create a index on the INVOICE_NO field
in the parent database. Then create a second index in the parent
database using the expression: INVERT(RECNO( )). In the child
(LINE_ITEM) database, go to the field rules and create a lookup on
the INVOICE_NO field. Set Auto Pop-up to "YES". Specify the parent
database (INVOICE) as the lookup database. The linking index will be
the one you created on the INVOICE_NO field in the parent database.
The display index will be the one you created using the expression
INVERT(RECNO( )). On the lookup mapping screen, specify the
INVOICE_NO as the only choice and filled field.

During data entry, when you "Zoom" to the LINE_ITEM database to enter
each line item this lookup will automatically pop-up with the last
entered invoice number at the top of the lookup window. Selecting the
top choice will fill in the INVOICE_NO field so the user doesn't have
to remember it.

Lookups; linking an INVENTORY database to the LINE_ITEM database:
If you add a database containing all the items you sell (INVENTORY),
you can then attach this as a lookup to the LINE_ITEM database using
a linking index based on the PART_NO field. Choose the DESC and PRICE
fields as filled fields. If you are using option 2 above simply link
the lookup to PART1, PART2, etc. This will make data entry quicker
and more accurate. You should only need to type in the part number
and quantity and the program should fill in the description and
price.

Using a script to make zooming easier & more efficient (Alpha FOUR
version 2 only):
The scripting feature in Alpha FOUR version 2 allows you to set the
zoom trigger (%SYS_TRIG_ZOOM) to automatically store the INVOICE_NO
value into a variable (%INVNO) and automatically place this into all
new records in the child.

First create a script that will set %SYS_TRIG_ZOOM to the name of the
script to play when a person zooms (COPYFLD). The contents of this
script are as follows:
{SET %SYS_TRIG_ZOOM, "COPYFLD"}

Then create a script called COPYFLD that copies the value in the
INVOICE_NO field in the INVOICE database into the variable %INVNO,
then zooms and enters this value into the INVOICE_NO field (in this
case this is the first field on the child form):
{SET %INVNO, INVOICE_NO}{TRIGKEYS {e{%INVNO}{ENTER}}}
For more on scripts and triggers see the Alpha FOUR version 2
reference manual.
Using another child database for customers:
If your company is like most, you will have more than one invoice for
the same customer. To economize you may want to add a CUSTOMER child
database to store customer information. Link this to the INVOICE
database based on CUST_ID or the customer name. This is beneficial in
two ways:
You only enter the customer information once (saves time and disk
space).
Address changes can be done once in the CUSTOMER database. Because
the INVOICE database gets it's customer information from the CUSTOMER
database, all invoices for that customer will now reflect the new
address for that customer.


Applying items sold against an "in stock", inventory database:
If you have an INVENTORY database containing all items you sell and
the current quantities in stock (IN_STOCK), you can apply the items
sold each day against this INVENTORY database using the Post utility.
Simply create an index in the INVENTORY database on PART_NO, use the
Post utility to post the LINE_ITEM database against the INVENTORY
database and specifically instruct Alpha FOUR to subtract the QTY
field in the LINE_ITEM database from the IN_STOCK field in the
INVENTORY database. Alpha FOUR should then look for a matching
PART_NO and when it finds it, subtract the quantity sold from the
quantity in stock.

NOTE: To prevent a line item from being applied to the quantity in
stock every time you post you will probably want to add a field
(POSTED) to the LINE_ITEM database. Perform a Global Update on this
field to place a "*" each time this post is performed. Each time you
run the post use a filter: ISBLANK(POSTED). This prevents already
posted line items from being applied against the inventory again.

To automate this, you can create a script or application that first
performs the post and then performs a Global Update to update the
POSTED field.


The suggestions contained here are only that, suggestions. You will
no doubt want to enhance these basic setups to meet your own needs.
We hope that you have found the information contained here to be
useful.